# -*- coding:utf-8 -*-
import json
import os
import uuid
from datetime import datetime, timedelta

import redis
import torndb
import xlrd

import setting

class ImportProductExcel(object):
    """
    商品通过Excel文件形式导入

    """

    def __init__(self):

        self.excel_filepath = u'/home/dev/zzh/import/'  # 文件存放目录
        self.product_excel_file = self.excel_filepath + u'product.xlsx'  # excel文件

        self.db = torndb.Connection(
            host=setting.mysql_host,
            database=setting.mysql_database,
            user=setting.mysql_user,
            password=setting.mysql_password,
            charset='utf8'
        )

        self.redis = redis.StrictRedis(host=setting.redis_host, port=setting.redis_port)

    def _get_excel_data(self, filename):
        """
        读取excel数据
            1）校验excel格式数据
            2）写入list

        :param file: 导入的excel文件
        :return:
        """
        try:
            data = xlrd.open_workbook(filename)
        except Exception, e:
            print str(e)
            return

        table = data.sheets()[0]
        nrows = table.nrows  # 行数
        ncols = table.ncols  # 列数
        if ncols < 7:
            print "ncols < 7"
            return

        if nrows < 10:
            print "nrows < 10"
            return

        cur_row = table.row_values(2)  # 某一行数据
        curr = {}
        curr['name'] = cur_row[0].encode('utf-8')
        curr['tag'] = cur_row[1].encode('utf-8')
        curr['vip'] = cur_row[2].encode('utf-8')
        curr['price'] = int(cur_row[3] * 100)
        curr['theme'] = cur_row[4].encode('utf-8')
        curr['channelGroup'] = cur_row[5].encode('utf-8')
        curr['desc'] = cur_row[6].encode('utf-8')
        curr['login_name'] = cur_row[7].encode('utf-8')
        self.curr_data = curr

        lesson_list = []
        for rownum in range(9, nrows):
            row = table.row_values(rownum)
            if row:
                if len(row[0]) == 0:
                    continue

                app = {}
                app['name'] = row[0].encode('utf-8')
                app['open_time'] = xlrd.xldate.xldate_as_datetime(row[4], 0)
                app['pic_cut_time'] = float(row[5])
                app['video_file'] = row[6].encode('utf-8')
                if not str(app['video_file']).endswith(".mp4"):
                    app['video_file'] += ".mp4"
                lesson_list.append(app)

        return lesson_list


    def _check_product_name_exist(self, product_name):
        """
        校验该商品名称是否存在，防止重复导入

        :param product_name: 商品名称
        :return:
        """
        pass

    def _insert_product(self, params):
        """
        将商品插入t_product表

        :param params:
        :return:
        """
        self.db.execute(
            "insert into t_curriculum (id, curr_name, teacher_id,curr_img,curr_img_bigger,curr_abstract, curr_price,"
            "total_lessons,capacity,t_curriculum_theme_id,vip,lesson_last_create_date,"
            "lesson_last_enable_date,create_date, update_date,del_flag) "
            "values (%(id)s,%(curr_name)s,%(teacher_id)s,%(curr_img)s,%(curr_img)s,%(curr_abstract)s, %(curr_price)s,"
            "%(total_lessons)s,%(capacity)s,%(t_curriculum_theme_id)s,%(vip)s,"
            "%(lesson_last_create_date)s,%(lesson_last_enable_date)s,%(create_date)s, %(update_date)s,2)",
            **params)

    def runner(self):

        if not os.path.exists(self.product_excel_file):
            print "Excel文件:{} 不存在!".format(self.product_excel_file)
            return

        print "开始检查Excel文件:{} ...".format(self.product_excel_file)

        products = self._get_excel_data(self.product_excel_file)
        if not products:
            print "没有需要导入的商品数据."
            return

        for product in products:
            pass

if __name__ == '__main__':
    print "run start : {}".format(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    ipe = ImportProductExcel()
    ipe.runner()
    ipe.db.close()
    print "run end : {}".format(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))